Content writing is the process of writing, editing, and publishing content in a digital format.
That content can include blog posts, video or podcast scripts, ebooks or whitepapers, press releases, product category descriptions, landing page or social media copy and more.
The SQL statement DROP USER allows you to delete a user from an existing database. Here is the syntax of the
DROP USER statement
DROP USER [IF EXISTS] user_name;
In the syntax above, don't forget to replace your use name with the user_name after the keywork
DROP USER. If the user doesn't exist in the current database then
DROP USER statement will fail.
To avoid this, you can use the IF EXISTS option. If the user already exists, the
IF EXISTS option conditionally deletes them.
1. Using the DROP USER statement to delete a user
Now, use the MyCollegeDb sample database for the following
DROP USER statement example.
First, create a new login testLogin with a password,
USE MyCollegeDb
GO
CREATE USER MyUser
FOR LOGIN testLogin;
Third, drop the user MyUser from the current database
USE MyCollegeDb
GO
DROP USER IF EXISTS MyUser;
2. Drop a user that owns a securable
First, create a new login called jacob with a password,
USE master
GO
CREATE LOGIN jacob
WITH PASSWORD = 'Jac.B908$#'
Second, use your current database MyCollegeDb and create a new user for the login
jacob
USE MyCollegeDb
GO
CREATE USER ami
FOR LOGIN jacob;
Third, create a schema called report and grant authorization to the user
ami.
USE MyCollegeDb
GO
CREATE SCHEMA report
AUTHORIZATION ami;
Fourth, connect to the SQL Server using the login jacob and create a table called
daily_sales in the schema report
USE MyCollegeDb
GO
CREATE TABLE report.daily_sales (
Id INT IDENTITY PRIMARY KEY,
CreationDate DATETIME NOT NULL,
Amount DECIMAL(10,2) NOT NULL DEFAULT 0
)
Fifth, switch the connection to the system administrator (sa) account and drop the user
ami
USE MyCollegeDb
GO
DROP USER ami;
The SQL server Returns the following error,
Because the user ami owns the schema report, the
DROP USER statement cannot delete it.
To delete user ami, you must first transfer the authorization of the schema report to another user. For example, the following statement changes the authorization of the schema
report to user dbo:
USE MyCollegeDb
GO
ALTER AUTHORIZATION
ON SCHEMA::report
TO dbo;
If you execute the DROP USER statement to delete the user ami, it will executes successfully,
Ashutosh Kumar Verma
16-Jul-2024SQL Server DROP USER
The SQL statement
DROP USER
allows you to delete a user from an existing database. Here is the syntax of theDROP USER
statementIn the syntax above, don't forget to replace your use name with the
user_name
after the keyworkDROP USER
. If the user doesn't exist in the current database thenDROP USER
statement will fail.To avoid this, you can use the
IF EXISTS
option. If the user already exists, theIF EXISTS
option conditionally deletes them.1. Using the DROP USER statement to delete a user
Now, use the
MyCollegeDb
sample database for the followingDROP USER
statement example.First, create a new login
testLogin
with a password,Second, create a new user and map it with the login
testLogin
Third, drop the user
MyUser
from the current database2. Drop a user that owns a securable
First, create a new login called
jacob
with a password,Second, use your current database
MyCollegeDb
and create a new user for the loginjacob
Third, create a schema called
report
and grant authorization to the userami
.Fourth, connect to the SQL Server using the login
jacob
and create a table calleddaily_sales
in the schemareport
Fifth, switch the connection to the system administrator (
sa
) account and drop the userami
The SQL server Returns the following error,
Because the user
ami
owns the schemareport
, theDROP USER
statement cannot delete it.To delete user
ami
, you must first transfer the authorization of the schema report to another user. For example, the following statement changes the authorization of the schemareport
to userdbo
:If you execute the
DROP USER
statement to delete the userami
, it will executes successfully,Also, Read: Describe the different types roles in SQL Server.